﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Tool_System_Client
{
    class DBManager
    {
        private string m_sDateTimeFormat = "yyyy/MM/dd hh:mm:ss";
        private static DBManager instance;
        private static object _lock = new object();
        private string m_sSQLConnString = "";

        public static DBManager GetInstance()
        {
            if (instance == null)
            {
                lock (_lock)
                {
                    if (instance == null)
                    {
                        instance = new DBManager();
                    }
                }
            }
            return instance;
        }

        public bool Init(string sSQLConnString)
        {
            m_sSQLConnString = sSQLConnString;

            return true;
        }

        public bool Login(string sName, string sPassword)
        {
            string strSQL = "select * from TS_S_T_P_Stuff where f_workercode='" + sName.Trim() + "' and f_password='" + DESEncrypt.Encrypt(sPassword.Trim()) + "' and f_del=0";

            SqlConnection conn = new SqlConnection(m_sSQLConnString);
            conn.Open();
            SqlCommand comm = new SqlCommand(strSQL, conn);
            SqlDataReader read = comm.ExecuteReader();
            if (read.HasRows)
            {
                read.Read();

                DataCache.GetInstance().OUserInfo.BLogin = true;
                DataCache.GetInstance().OUserInfo.IID = int.Parse(read["f_id"].ToString().Trim());
                DataCache.GetInstance().OUserInfo.SUserCode = read["f_workercode"].ToString();
                DataCache.GetInstance().OUserInfo.SUserName = read["f_name"].ToString();

                return true;
            }

            DataCache.GetInstance().OUserInfo.Clear();
            return false;
        }

        private void FilledDataTable(string strSQL, DataTable dt)
        {
            try
            {
                SqlConnection MyClassConn = new SqlConnection(m_sSQLConnString);
                SqlDataAdapter sda = new SqlDataAdapter(strSQL, MyClassConn);
                sda.Fill(dt);
                sda.Dispose();
                dt.Dispose();
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
        }

        public void MySqlCom(string strSql)
        {
            SqlConnection MyClassConn = new SqlConnection(m_sSQLConnString);
            SqlCommand MyClassComm = new SqlCommand(strSql, MyClassConn);
            MyClassConn.Open();
            MyClassComm.ExecuteNonQuery();
            MyClassConn.Close();
            MyClassConn.Dispose();
        }

        public string MySqlComGetID(string strSql)
        {
            SqlConnection MyClassConn = new SqlConnection(m_sSQLConnString);
            strSql = strSql + ";select @@IDENTITY As 'Identity'";
            SqlCommand MyClassComm = new SqlCommand(strSql, MyClassConn);
            MyClassConn.Open();
            string Index = (MyClassComm.ExecuteScalar()).ToString().Trim();
            MyClassConn.Close();
            MyClassConn.Dispose();
            return Index;
        }

        #region 工具数据
        // 获取工具列表
        public DataTable GetNormalToolList()
        {
            DataTable dt = new DataTable();

            string strSQL = "select a.*, b.f_name as f_tooltypename, b.f_picture, c.f_name as f_posname " +
                "from TS_Tool_Tools as a " +
                "left join TS_Tool_Type as b on a.f_typeid=b.f_id " +
                "left join TS_Tool_Pos as c on a.f_position=c.f_id " +
                "where a.f_del=0 and a.f_statetype=0 and a.f_parentid<=0";

            FilledDataTable(strSQL, dt);

            return dt;
        }

        public DataTable GetToolList()
        {
            DataTable dt = new DataTable();

            string strSQL = "select a.*, b.f_name as f_tooltypename, b.f_picture, c.f_name as f_posname " +
                "from TS_Tool_Tools as a " +
                "left join TS_Tool_Type as b on a.f_typeid=b.f_id " +
                "left join TS_Tool_Pos as c on a.f_position=c.f_id " +
                "where a.f_del=0 and a.f_parentid<=0";

            FilledDataTable(strSQL, dt);

            return dt;
        }

        public bool ApplyTools(List<int> listTools)
        {
            try
            {
                string strSQL = "update TS_Tool_Tools set f_statetype = 1 where ";

                for (int i = 0; i < listTools.Count(); ++i)
                {
                    if (i == 0)
                    {
                        strSQL = strSQL + " f_id= " + listTools[i];
                    }
                    else
                    {
                        strSQL = strSQL + " or f_id= " + listTools[i];
                    }
                }

                MySqlCom(strSQL);

                return true;
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }

            return false;
        }
        #endregion

        public bool ApplyOrder(string sUserCode, DateTime tTime, List<int> listTools)
        {
            try
            {
                string strSQL = "INSERT INTO TS_Tool_Order (f_proposid, f_propostime) VALUES (" + 
                    sUserCode + ", '" + tTime.ToString(m_sDateTimeFormat) + "') ";

                string sOrderID = MySqlComGetID(strSQL);

                foreach (int iToolID in listTools)
                {
                    strSQL = "INSERT INTO TS_Tool_OrderDetail (f_orderid, f_toolid) VALUES (" + sOrderID + ", " + iToolID + ") ";
                    MySqlCom(strSQL);
                }

                return true;
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }

            return false;
        }

        #region 工单数据
        public DataTable GetOrderByUser(int iUserCode)
        {
            DataTable dt = new DataTable();

            string strSQL = "select a.*, b.f_name as f_proposname, c.f_name as f_sendname, d.f_name as f_recivename " +
                "from TS_Tool_Order as a " +
                "left join TS_S_T_P_Stuff as b on a.f_proposid=b.f_workercode " +
                "left join TS_S_T_P_Stuff as c on a.f_sendid=c.f_workercode " +
                "left join TS_S_T_P_Stuff as d on a.f_reciveid=d.f_workercode " +
                "where a.f_del=0 and a.f_proposid=" + iUserCode;

            FilledDataTable(strSQL, dt);

            return dt;
        }

        // 获取订单详情
        public DataTable GetOrderDetail(int iID)
        {
            DataTable dt = new DataTable();

            string strSQL = "select a.f_orderid, a.f_toolid, b.f_statetype, b.f_rfid, c.f_name, d.f_name as f_posname " +
                "from TS_Tool_OrderDetail as a " +
                "left join TS_Tool_Tools as b on a.f_toolid=b.f_id " +
                "left join TS_Tool_Type as c on b.f_typeid=c.f_id " +
                "left join TS_Tool_Pos as d on b.f_position=d.f_id " +
                "where a.f_del=0 and a.f_orderid=" + iID.ToString();
            FilledDataTable(strSQL, dt);

            return dt;
        }
        #endregion

    }
}
